# This is a sample Python script.
# -*- coding: utf-8 -*-

from PyQt6.QtWidgets import QApplication, QMainWindow
from MainWin import *
import pymysql
import sys


# connection = pymysql.connect(host='localhost',
#                              port=3306,
#                              user='root',
#                              passwd='x0',
#                              db='finalTest')
# try:
#     with connection.cursor() as cursor:
#         # 以查询 courses 表为例
#         sql = "SELECT * FROM student"
#         cursor.execute(sql)
#         for result in cursor:
#             # 输出查询结果
#             print(result)
# finally:
#     connection.close()

# studentNo = 0
# studentClass = 0
# studentName = '0'
# studentSex = '0'
# studentGrade = '0'
# studentBirthday = '0'

# MyMainWindow  GUI中的主窗口
class MyMainWindow(QMainWindow, Ui_MainWindow):
    # 1. 为类的实例变量绑定属性，通过def_init_(self)方法，在创建实例变量的时候，把属性绑定
    # _init_方法的第一个参数永远是self,表示创建的实例本身
    def __init__(self, parent=None):
        super(MyMainWindow, self).__init__(parent)
        # 初始化窗口 self--MyMainWindow的实例
        self.setupUi(self)

        # 信号与槽 qt
        # 1.录入学生按钮绑定
        # 无参数时不需要lambda和括号
        self.pushButton_inpuitStudent.clicked.connect(self.inputStudent)
        # 2. 按学号查询学生按钮绑定
        self.pushButton_searchStudentNo.clicked.connect(self.searchStudentNo)
        # 2. 按姓名查询学生按钮绑定
        self.pushButton_searchStudentName.clicked.connect(self.searchStudentName)
        # 2. 按专业查询学生按钮绑定
        self.pushButton_searchStudentSpecialty.clicked.connect(self.searchStudentSpecialty)
        # 3. 录入一位学生一门课的成绩
        self.pushButton_inputGrade.clicked.connect(self.inputGrade)
        # 4. 查询一位学生的课程按钮绑定
        self.pushButton_searchCourse.clicked.connect(self.searchCourse)
        # 把所有课程的平均成绩和必修课程的平均成绩后的lineEdit设为只读
        self.lineEdit_allCourse.setReadOnly(True)
        self.lineEdit_mustCourse.setReadOnly(True)
        # 5. 查询一位学生被哪些教师教过课按钮绑定
        self.pushButton_searchTeacher.clicked.connect(self.searchTeacher)
        # 6. 查询即将被开除的学生按钮绑定
        self.pushButton_searchToBeFired

    # 1. 录入一位学生，应包含学号、姓名、性别、出生年月、班级等信息。
    def inputStudent(self):
        # 新记录
        global connection
        studentNo = int(self.lineEdit_studentNo.text())
        studentClass = self.lineEdit_studentClass.text()
        studentName = self.lineEdit_studentName.text()
        studentSex = self.lineEdit_studentSex.text()
        studentGrade = self.lineEdit_studentGrade.text()
        studentBirthday = self.lineEdit_studentBirthday.text()

        # 检验下能否从qt读到text
        print('studentNo: ' + str(
            studentNo) + ' studentClass: ' + studentClass + ' studentName: ' + studentName + ' studentSex: ' + studentSex + ' studentGrade: ' + studentGrade + ' studentBirthday: ' + studentBirthday)

        try:
            connection = pymysql.connect(host='localhost', port=3306, user='root', password='x0',
                                         db='finalTest')
            cursor = connection.cursor()
            # 这个语法抄的菜鸟教程
            sql = """INSERT INTO student(studentNo,studentClass,studentName,studentSex,studentGrade,
                    studentBirthday)VALUES(%d,'%s','%s','%s','%s','%s')""" % (
                studentNo, studentClass, studentName, studentSex, studentGrade, studentBirthday)
            # 执行sql语句
            cursor.execute(sql)
            # 提交到数据库执行
            connection.commit()
            print('1.录入学生 success')
        except Exception as exc:
            # 如果发生错误则回滚 这也是抄的菜鸟教程
            connection.rollback()
            print('1.录入学生 error')
            # 输出报错 很重要
            print(str(exc))
        finally:
            # 关闭数据库连接
            connection.close()

    # 2. 按学号、姓名、专业三种方式查询学生基本信息
    # (1) 按学号查询学生基本信息
    def searchStudentNo(self):
        # 通过qt lineEdit组件获取用户输入的学号
        global connection
        studentNo = int(self.lineEdit_serarchStudentNo.text())
        try:
            connection = pymysql.connect(host='localhost', port=3306, user='root', password='x0',
                                         db='finalTest')
            cursor = connection.cursor()
            sql = "SELECT * FROM student WHERE studentNo = %d" % studentNo
            cursor.execute(sql)
            results = cursor.fetchall()
            for row in results:
                # row为行，每行0-5列
                studentNo = row[0]
                studentClass = row[1]
                studentName = row[2]
                studentSex = row[3]
                studentGrade = row[4]
                studentBirthday = row[5]
                # 控制台输出结果
                print('studentNo: ' + str(
                    studentNo) + ' studentClass: ' + studentClass + ' studentName: ' + studentName + ' studentSex: ' + studentSex + ' studentGrade: ' + studentGrade + ' studentBirthday: ' + studentBirthday)
        except Exception as exc:
            # 如果发生错误则回滚 这也是抄的菜鸟教程
            connection.rollback()
            print('1.按学号查学生 error')
            # 输出报错 很重要
            print(str(exc))
        finally:
            # 关闭数据库连接
            connection.close()

    # (2) 按姓名查询学生基本信息
    def searchStudentName(self):
        # 通过qt lineEdit组件获取用户输入的学号
        global connection
        studentName = self.lineEdit_searchStudentName.text()
        try:
            connection = pymysql.connect(host='localhost', port=3306, user='root', password='x0',
                                         db='finalTest')
            cursor = connection.cursor()
            sql = "SELECT * FROM student WHERE studentName = '%s'" % studentName
            cursor.execute(sql)
            results = cursor.fetchall()
            for row in results:
                # row为行，每行0-5列
                studentNo = row[0]
                studentClass = row[1]
                studentName = row[2]
                studentSex = row[3]
                studentGrade = row[4]
                studentBirthday = row[5]
                # 控制台输出结果
                print('studentNo: ' + str(
                    studentNo) + ' studentClass: ' + studentClass + ' studentName: ' + studentName + ' studentSex: ' + studentSex + ' studentGrade: ' + studentGrade + ' studentBirthday: ' + studentBirthday)
        except Exception as exc:
            # 如果发生错误则回滚 这也是抄的菜鸟教程
            connection.rollback()
            print('2.按姓名查学生 error')
            # 输出报错 很重要
            print(str(exc))
        finally:
            # 关闭数据库连接
            connection.close()

    # (3) 按专业查询学生基本信息
    def searchStudentSpecialty(self):
        # 通过qt lineEdit组件获取用户输入的学号
        global connection
        studentSpecialty = self.lineEdit_searchStudentSpecialty.text()
        try:
            connection = pymysql.connect(host='localhost', port=3306, user='root', password='x0',
                                         db='finalTest')
            cursor = connection.cursor()
            sql = "SELECT * FROM student WHERE studentSpecialty = '%s'" % studentSpecialty
            cursor.execute(sql)
            results = cursor.fetchall()
            for row in results:
                # row为行，每行0-5列
                studentNo = row[0]
                studentClass = row[1]
                studentName = row[2]
                studentSex = row[3]
                studentGrade = row[4]
                studentBirthday = row[5]
                # 控制台输出结果
                print('studentNo: ' + str(
                    studentNo) + ' studentClass: ' + studentClass + ' studentName: ' + studentName + ' studentSex: ' + studentSex + ' studentGrade: ' + studentGrade + ' studentBirthday: ' + studentBirthday)
        except Exception as exc:
            # 如果发生错误则回滚 这也是抄的菜鸟教程
            connection.rollback()
            print('2.按专业查学生 error')
            # 输出报错 很重要
            print(str(exc))
        finally:
            # 关闭数据库连接
            connection.close()

    # 3. 录入一位学生一门课的成绩
    def inputGrade(self):
        # 新记录
        global connection
        studentNo = int(self.lineEdit_studentNo2.text())
        courseNo = self.lineEdit_courseNo.text()
        firstGrade = int(self.lineEdit_grade.text())

        # 检验下能否从qt读到text
        print('studentNo: ' + str(
            studentNo) + ' courseNo: ' + courseNo + ' firstGrade: ' + str(firstGrade))

        try:
            connection = pymysql.connect(host='localhost', port=3306, user='root', password='x0',
                                         db='finalTest')
            cursor = connection.cursor()
            # 这个语法抄的菜鸟教程
            sql = """INSERT INTO grade(studentNo,courseNo,firstGrade)VALUES(%d,'%s',%d)""" % (
                studentNo, courseNo, firstGrade)
            # 执行sql语句
            cursor.execute(sql)
            # 提交到数据库执行
            connection.commit()
            print('3.录入成绩 success')
        except Exception as exc:
            # 如果发生错误则回滚 这也是抄的菜鸟教程
            connection.rollback()
            print('1.录入成绩 error')
            # 输出报错 很重要
            print(str(exc))
        finally:
            # 关闭数据库连接
            connection.close()

    # 4. 查询一位学生所修的课程、性质（必修或选修）、学期、学分及成绩；查询他的必修课平均成绩、所有课程平均成绩（平均成绩应按学分加权）
    def searchCourse(self):
        global connection
        studentNo = int(self.lineEdit_studentNo3.text())
        try:
            connection = pymysql.connect(host='localhost', port=3306, user='root', password='x0',
                                         db='finalTest')
            cursor = connection.cursor()
            # 通过学号去查grade表和teachingPlan表联合(借助学号和专业名)查课程，性质，学期，学分和成绩
            sql = "SELECT courseName, ifCompulsory, term, credit, firstGrade FROM teachingPlan, grade WHERE grade.courseNO = teachingPlan.courseNo AND grade.specialtyName = teachingPlan.specialtyName AND grade.studentNo = %d" % studentNo
            cursor.execute(sql)
            results = cursor.fetchall()
            for row in results:
                # row为行，每行0-5列
                courseName = row[0]
                ifCompulsory = row[1]
                term = row[2]
                credit = row[3]
                firstGrade = row[4]
                # 控制台输出结果
                print(
                    'courseName: ' + courseName + ' ifCompulsory: ' + ifCompulsory + ' term: ' + term + ' credit: ' + str(
                        credit) + ' firstGrade: ' + str(firstGrade))

            # 查询他的全部课程 加权学分平均成绩
            allGradeTimeCredit = 0
            allCredit = 0
            for row in results:
                allGradeTimeCredit += row[3] * row[4]
                allCredit += row[3]
            print('所有课程平均成绩为: ' + str(allGradeTimeCredit / allCredit))
            # 通过qt显示 加入math.floor 进行数字的取舍
            self.lineEdit_allCourse.setText(str(round(allGradeTimeCredit / allCredit, 2)))

            # 查询他的必修课平均成绩，按学分加权
            allMustGradeTimeCredit = 0
            allMustCredit = 0
            for row in results:
                if row[1] == '必修':
                    allMustGradeTimeCredit += row[3] * row[4]
                    allMustCredit += row[3]
            print('必修课平均成绩为: ' + str(allMustGradeTimeCredit / allMustCredit))
            # 通过qt显示
            self.lineEdit_mustCourse.setText(str(round(allMustGradeTimeCredit / allMustCredit, 2)))

        except Exception as exc:
            # 如果发生错误则回滚 这也是抄的菜鸟教程
            connection.rollback()
            print('4.查学生课程 error')
            # 输出报错 很重要
            print(str(exc))
        finally:
            # 关闭数据库连接
            connection.close()

    # 5. 查询一位学生被哪些教师教过课
    def searchTeacher(self):
        global connection
        studentNo = int(self.lineEdit_studentNo4.text())
        try:
            connection = pymysql.connect(host='localhost', port=3306, user='root', password='x0',
                                         db='finalTest')
            cursor = connection.cursor()
            # 利用学号在student中查看其所在班，然后根据某个班某课由哪些老师教来确认老师交了这个学生
            sql = "SELECT courseTeacher FROM courseArrangement, student WHERE courseArrangement.studentClass = student.studentClass AND studentNo = %d" % studentNo
            cursor.execute(sql)
            results = cursor.fetchall()
            for row in results:
                print(row[0])
        except Exception as exc:
            # 如果发生错误则回滚 这也是抄的菜鸟教程
            connection.rollback()
            print('5.查找教课老师 error')
            # 输出报错 很重要
            print(str(exc))
        finally:
            # 关闭数据库连接
            connection.close()

    # 6. 查询快要被开除的学生
    def searchToBeFired(self):
        try:
            connection = pymysql.connect(host='localhost', port=3306, user='root', password='x0',
                                         db='finalTest')
            cursor = connection.cursor()
            # 借助grade表和teachingPlan表，根据courseNo连接，这里第一次我先只查所有课程学分总和快要挂的北锅童鞋
            sql = "SELECT studentNo FROM grade, teachingPlan WHERE grade.courseNo = teachingPlan.courseNo AND firstGrade < 60"
        #     SELECT credit
        except Exception as exc:
            # 如果发生错误则回滚 这也是抄的菜鸟教程
            connection.rollback()
            print('6.查找被开除 error')
            # 输出报错 很重要
            print(str(exc))
        finally:
            # 关闭数据库连接
            connection.close()


if __name__ == "__main__":
    # 以下为固定格式
    app = QApplication(sys.argv)
    myWin = MyMainWindow()
    myWin.show()
    # 设置窗口名称
    myWin.setWindowTitle("学籍管理系统")
    sys.exit(app.exec())
